Understanding Replication Fundamentals – Detailed Notes

Course: MySQL Training

Topic: Understanding Replication Fundamentals

Course Link: MySQL Training Course – JustAcademy

Demo Link: Register for a Course Demo

1. Introduction to MySQL Replication

MySQL replication is a database feature that copies data and database changes from one MySQL server to one or more other MySQL servers. It helps organizations maintain additional copies of their databases for reporting, read scaling, disaster recovery, and high availability architectures.

In a typical replication setup, one server acts as the source and one or more other servers act as replicas. The source records database changes, and replicas receive and apply those changes so that their data can follow the source.

Replication is widely used in applications that need to serve many read requests, keep a separate reporting database, or maintain a copy of important data on another server. However, replication does not automatically guarantee immediate consistency, automatic failover, or a complete backup strategy. These capabilities require appropriate design and additional operational controls.

1.1 Why Replication Is Important

• Read scalability: Some read workloads can be distributed across replicas.
• Reporting and analytics: Reporting queries can run against a replica instead of competing directly with application traffic on the source.
• Disaster recovery: A replica can provide an additional copy of data if the source becomes unavailable, subject to replication lag and failure scenarios.
• High availability architecture: Replication can be a building block for systems designed to recover from server failures.
• Geographical distribution: Replicas can be placed closer to certain application regions, although network latency and consistency must be considered.
• Maintenance flexibility: Replicas can help with selected maintenance and testing workflows when carefully managed.

2. Learning Objectives

After studying this topic, learners should be able to:

• Explain MySQL replication and its main purpose.
• Identify source and replica servers.
• Understand binary logs and replication events.
• Describe how a replica receives and applies database changes.
• Compare statement-based, row-based, and mixed binary logging formats.
• Understand asynchronous, semisynchronous, and group replication concepts.
• Recognize common replication topologies and use cases.
• Inspect replication status and identify replication lag.
• Understand replication security and troubleshooting fundamentals.
• Explain why replication complements but does not replace backups.

3. Basic Replication Terminology

4. How MySQL Replication Works

In classic MySQL source-to-replica replication, the source records changes in its binary log. The replica connects to the source, retrieves the relevant events, stores them in relay logs, and applies them to its own database.

4.1 Replication Workflow

1. Client executes a change: An application inserts, updates, or deletes data on the source.
2. Source records the change: The source writes relevant events to its binary log when binary logging is enabled and configured.
3. Replica connects: The replica uses its replication configuration and an authorized replication account to connect to the source.
4. Events are transmitted: The replica requests and receives binary log events from the source.
5. Relay log is updated: Received events are stored in relay logs on the replica.
6. Events are applied: Replica applier processes execute the replicated changes against the replica's data.
7. Status is monitored: Administrators inspect errors, transaction progress, and lag to confirm replication is operating as expected.

4.2 Replication Flow Diagram

Application
    |
    v
MySQL Source
    |
    | Binary Log Events
    v
Replica Receiver
    |
    v
Relay Log
    |
    v
Replica Applier
    |
    v
MySQL Replica

This diagram represents the basic flow for classic source-to-replica replication. Other MySQL replication architectures may use additional coordination or communication mechanisms.

5. Understanding the Source Server

The source server is the origin of the changes that replicas receive. It commonly handles application writes and maintains the binary log required for replication.

5.1 Responsibilities of the Source

• Accept database connections and execute authorized operations.
• Maintain the data that serves as the replication source.
• Write relevant events to the binary log.
• Retain binary logs long enough for replicas to retrieve required events.
• Authenticate replication connections and enforce appropriate permissions.
• Provide operational information needed to monitor replication.

5.2 Binary Logging

Binary logging is central to many MySQL replication configurations. It records events that describe data changes and selected operations. Replicas use these events to reproduce the changes locally.

A server configured to act as a replication source generally needs binary logging enabled. The exact requirements depend on the replication architecture and MySQL version.

Example configuration:

[mysqld]
server_id=101
log_bin=mysql-bin

In this example, server_id identifies the server within the replication environment, and log_bin enables binary logging with a specified log base name. Check the installed MySQL version and the server's existing configuration before applying these options.

6. Understanding the Replica Server

The replica maintains a copy of selected source data by receiving and applying changes. It can be used for read workloads, reporting, testing, or as part of a recovery strategy.

6.1 Responsibilities of the Replica

• Connect to the configured source.
• Authenticate using an authorized replication account.
• Retrieve binary log events.
• Store received events in relay logs.
• Apply the events in the appropriate order according to the replication configuration.
• Track replication progress and report errors.
• Provide read access where permitted by the database architecture.

6.2 Basic Replica Configuration Example

[mysqld]
server_id=102
relay_log=replica-relay-bin

The replica must have a unique server ID within the relevant replication environment. This snippet shows only example settings; it is not a complete replication configuration. Source connection details, replication credentials, initial data, log coordinates or GTID configuration, and other version-specific requirements must also be handled.

7. The Binary Log and Relay Log

7.1 Binary Log

The binary log is maintained on the source and contains events that can be transmitted to replicas. It can also support point-in-time recovery when combined with a suitable backup and the required sequence of binary logs.

Binary logs consume disk space, so administrators must establish appropriate retention and monitoring policies. Removing logs that a replica still needs can interrupt replication and require recovery work.

7.2 Relay Log

The relay log is maintained on the replica. It stores events that have been received from the source but may not yet have been applied.

Relay logs allow the receiving process and the applying process to work separately. The replica can receive events while the applier processes earlier events, subject to resource availability and replication configuration.

7.3 Binary Log vs Relay Log

8. Replication Threads and Processing

Classic MySQL replication uses processes or threads that receive events from the source and apply them on the replica. The exact thread model and status fields depend on the MySQL version and replication mode.

8.1 Receiver Thread

The receiver connects to the source and retrieves binary log events. The received events are written to the replica's relay log.

8.2 Applier Thread

The applier processes events from the relay log and executes the replicated operations on the replica. In modern MySQL versions, replication can use parallel applier workers when configured and supported.

8.3 Why Separate Processing Matters

Separating event retrieval from application helps the replica continue receiving events while it applies previously received events. If the applier cannot keep up, the relay log backlog can grow and replication lag can increase.

9. Types of MySQL Replication

9.1 Asynchronous Replication

Asynchronous replication is the traditional default model for MySQL replication. The source commits a transaction without waiting for a replica to confirm that the transaction has been received or applied.

Advantages:

• Usually adds little direct waiting for replica acknowledgment to the source's commit path.
• Supports read scaling and additional data copies.
• Can work across network links with some latency.

Limitations:

• Replicas can lag behind the source.
• A source failure can cause recently committed changes to be missing from a replica.
• Applications must account for possible stale reads from replicas.

9.2 Semisynchronous Replication

Semisynchronous replication adds an acknowledgment requirement for at least one participating replica, depending on configuration. The source waits for a replica to acknowledge receipt of the transaction's events to the required stage before completing the relevant commit acknowledgment to the client.

This can reduce the risk of losing transactions that have been acknowledged by the source, but it does not guarantee that the replica has already applied the transaction or that every failure scenario is covered.

Advantages:

• Provides an additional acknowledgment step compared with ordinary asynchronous replication.
• Can reduce certain data-loss risks during source failure.

Limitations:

• Network delays can increase transaction latency.
• Behavior depends on configuration and the number and state of participating replicas.
• It is not a substitute for backups or a complete high-availability design.

9.3 Group Replication

MySQL Group Replication is a separate technology for coordinating a group of MySQL servers. It uses group communication and transaction coordination mechanisms to support replicated group membership and consistency controls. It can form the basis of single-primary or multi-primary architectures, subject to the selected mode and operational requirements.

Group Replication is different from simply configuring a classic asynchronous source and replica. It has its own setup requirements, conflict considerations, recovery behavior, and monitoring needs.

9.4 Comparison of Replication Approaches

10. Replication Formats

MySQL supports different binary logging formats that determine how database changes are represented in the binary log and replicated to other servers.

10.1 Statement-Based Replication (SBR)

Statement-based replication records SQL statements that modify data. The replica executes the replicated statements to reproduce the changes.

Example:

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 10;

With statement-based logging, the SQL statement is recorded rather than a separate before-and-after description of every changed row.

Advantages:

• Can produce compact logs for some workloads.
• Records the SQL operations that were executed.
• May be efficient when a statement modifies many rows.

Limitations:

• Some statements may produce different results under different conditions.
• Nondeterministic functions and environment differences can create replication concerns.
• Statement safety and behavior must be evaluated carefully.

10.2 Row-Based Replication (RBR)

Row-based replication records changes to individual rows. Instead of relying on the replica to execute the original data-changing statement in the same way, the binary log contains row events representing the changes.

Advantages:

• Provides consistent representation of the row changes made on the source.
• Handles many statements whose results could be problematic under statement-based logging.
• Is commonly recommended for predictable replication behavior, including GTID-based replication.

Limitations:

• Large changes affecting many rows can produce substantial binary logs.
• Row events may be less convenient to interpret than ordinary SQL statements.
• Log volume depends on the data changed and the row image configuration.

10.3 Mixed-Based Replication (MIXED)

Mixed-based replication allows MySQL to use statement-based or row-based logging depending on the statement and the server's rules. It aims to use an appropriate format for different operations.

10.4 Comparison of Replication Formats

The logging format should be selected based on the application, MySQL version, operational requirements, and documented replication behavior.

11. Replication Using Binary Log Positions

One traditional method of configuring replication uses a binary log file name and a position within that file. These coordinates identify where the replica should begin retrieving events from the source.

11.1 Example of Binary Log Coordinates

Binary log file: mysql-bin.000004
Position:       157

The values above are illustrative. A real configuration must use the correct coordinates associated with a consistent initial copy of the source data.

11.2 Important Requirements

• The source must have binary logging enabled.
• Each server must have an appropriate unique server ID.
• The replica must start from coordinates consistent with its initial data.
• The required binary log files must still be available on the source.
• Network access and replication credentials must be configured securely.

Binary log positions can be useful in existing environments. However, administrators must track file names and positions accurately, especially when provisioning new replicas or changing sources.

12. Global Transaction Identifiers (GTIDs)

A Global Transaction Identifier, or GTID, identifies a transaction that originated on a MySQL server. GTID-based replication lets MySQL track transactions by their identifiers instead of requiring administrators to manage only binary log file names and positions.

12.1 Why GTIDs Are Useful

• They identify transactions across a replication topology.
• They simplify replica provisioning and source changes in supported workflows.
• They help determine which transactions have already been executed.
• They support automatic positioning when configuring a replica to use a GTID-enabled source.
• They can reduce manual bookkeeping during failover and scale-out operations.

12.2 GTID Format

A traditional GTID contains a source identifier and a transaction sequence number. The source identifier is normally based on the originating server's UUID.

source_uuid:transaction_number

Illustrative example:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

This example represents a transaction associated with a particular originating server and transaction number. The UUID and number are examples, not values to copy into a configuration.

12.3 GTID Auto-Positioning

With GTID auto-positioning, the replica communicates its executed transaction history to the source. MySQL determines which required transactions the replica still needs, rather than relying solely on manually supplied binary log coordinates.

Example of a GTID-related replication setting:

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = 'source.example.com',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'replication_user',
  SOURCE_AUTO_POSITION = 1;

This is a syntax illustration, not a complete deployment procedure. The source address, replication account, authentication, initial data, TLS settings where required, and GTID prerequisites must be configured correctly. Never use placeholder host names or credentials as if they were real production settings.

12.4 GTID-Based vs Position-Based Replication

13. Common Replication Topologies

A replication topology describes how the source and replica servers are connected. The best topology depends on read demand, recovery objectives, network design, operational complexity, and the application's consistency requirements.

13.1 Single Source and Single Replica

Source
   |
   v
Replica

This is a simple architecture in which one source replicates changes to one replica.

Use cases:

• Learning and development environments.
• A secondary copy for reporting.
• A starting point for understanding replication.

13.2 Single Source and Multiple Replicas

             +----> Replica 1
             |
Source ------+----> Replica 2
             |
             +----> Replica 3

One source sends changes to several replicas. Read-only application traffic or reporting workloads may be distributed across replicas when the application is designed for this purpose.

Advantages:

• Provides several copies of the data.
• Allows certain read workloads to be distributed.
• Supports separate reporting and operational workloads.

Considerations:

• Replicas may have different amounts of lag.
• Additional replicas increase monitoring and infrastructure requirements.
• Writes generally remain on the designated source unless the architecture explicitly supports another write model.

13.3 Chain Replication

Source
   |
   v
Replica A
   |
   v
Replica B

In a chain arrangement, one replica can act as an upstream source for another server when the configuration supports the required binary logging and replication behavior.

This topology can distribute replication across multiple stages, but it also adds dependencies. If an upstream link fails or falls behind, downstream replicas can be affected.

13.4 Multi-Source Replication

MySQL multi-source replication allows a replica to receive transactions from more than one source through separate replication channels. It can be useful for consolidating data from multiple systems.

Multi-source replication requires careful database design. If different sources modify overlapping data, conflicts and inconsistent outcomes may occur unless the application and schema are designed to prevent them.

13.5 Group Replication Topology

Group Replication uses a coordinated group of MySQL servers and supports single-primary or multi-primary modes. It is designed for use cases requiring group membership and transaction coordination rather than simply maintaining an independent asynchronous copy.

Group Replication must be planned and configured according to its own requirements. It should not be treated as interchangeable with a basic asynchronous replication setup.

14. Read Scaling with Replication

Read scaling means distributing suitable read queries across multiple database servers to reduce the workload on a single server.

14.1 Example Application Architecture

Application
    |
    +---- Write Queries ----> Source
    |
    +---- Read Queries -----> Replica 1
    |
    +---- Reports ----------> Replica 2

In this example, writes go to the source, general reads may go to one replica, and reporting queries may go to another. A connection router or application-level routing logic can direct requests to the appropriate server.

14.2 Benefits

• Reduces some read workload on the source.
• Allows reporting queries to run separately from application writes.
• Provides flexibility when allocating database resources.
• Can improve throughput for workloads dominated by suitable reads.

14.3 Read-After-Write Consistency

Asynchronous replicas may not have applied a recent transaction when the application queries them. For example, a user may update an account profile and immediately read the old value from a lagging replica.

Possible strategies include:

• Send the immediate follow-up read to the source.
• Use a carefully designed consistency check before reading from a replica.
• Route selected sessions or transactions to the source when read-after-write consistency is required.
• Use an architecture and replication mode that meets the application's specific consistency requirements.

Do not assume that a replica always contains the latest committed data.

15. Replication Lag

Replication lag is the delay between a transaction being committed on the source and its changes being applied on a replica. Lag can occur because of network delays, large transactions, limited replica resources, heavy workloads, locks, or a replication error.

15.1 Common Causes of Replication Lag

• High write activity on the source.
• Large updates or deletes that require substantial work on the replica.
• Slow storage or insufficient CPU on the replica.
• Network interruptions or high network latency.
• Long-running transactions or contention.
• Limited parallel replication capacity for the workload.
• Replication errors that stop the applier.
• Resource contention caused by reporting or analytical queries.

15.2 Why Lag Matters

• Applications may read outdated values.
• Reports may not reflect the latest transactions.
• A replica may not contain the most recent changes during a failure.
• Recovery and failover planning may be affected.

15.3 Monitoring Replication Status

For classic replication, the following command provides useful diagnostic information in supported MySQL versions:

SHOW REPLICA STATUS\G

Important fields may include:

Field names and output vary by MySQL version and replication configuration. In newer versions, the replica terminology is preferred; older installations may use legacy terminology such as SHOW SLAVE STATUS.

16. Replication Security

Replication connections carry database events and must be protected like other database connections. A weakly protected replication account or unencrypted network path can expose the environment to unnecessary risk.

16.1 Use a Dedicated Replication Account

Create a separate account for replication rather than reusing an application account or an unrestricted administrator account. Grant only the privileges required for the replication method and MySQL version.

Example for a training environment:

CREATE USER 'replication_user'@'10.0.0.%'
IDENTIFIED BY 'Replace-With-A-Strong-Secret';

GRANT REPLICATION SLAVE
ON *.*
TO 'replication_user'@'10.0.0.%';

This example uses a sample network pattern and placeholder password. Replace them with an appropriately restricted host pattern and a secure secret. Verify the exact privilege requirements for your MySQL version, and avoid broad host patterns when a narrower source address is possible.

16.2 Additional Security Practices

• Use encrypted connections for replication where required by the security design.
• Restrict network access to the database port.
• Keep replication credentials out of public source code and logs.
• Apply least-privilege permissions.
• Restrict access to binary logs, relay logs, and database backups.
• Monitor failed connection attempts and replication authentication errors.
• Rotate credentials through a tested process.
• Review access controls regularly.

17. Replication and Backups

Replication provides additional data copies, but it does not replace a proper backup strategy. Many accidental changes made on the source can also be replicated to the replica.

17.1 Why Replication Is Not a Backup Replacement

• An accidental deletion may be copied to replicas.
• Malicious or unauthorized changes may spread through the replication stream.
• Corruption or configuration mistakes can affect multiple servers.
• A replica can lag behind the source and may not contain the latest transactions.
• Binary logs and relay logs are not substitutes for a verified restorable backup.

17.2 Recommended Backup Practices

1. Maintain regular database backups appropriate to the recovery objectives.
2. Store backup copies separately from the production database infrastructure.
3. Protect backups with access controls and encryption where appropriate.
4. Use binary logs for point-in-time recovery when the backup and logging setup supports it.
5. Test restore procedures regularly.
6. Monitor backup success and retention.
7. Document recovery procedures and responsibilities.

18. Common Replication Problems and Troubleshooting

18.1 Troubleshooting Procedure

1. Confirm that the source and replica servers are running.
2. Check network connectivity and replication account authentication.
3. Run SHOW REPLICA STATUS\G on the replica where supported.
4. Inspect receiver and applier status and the last recorded errors.
5. Check the MySQL error log on the affected servers.
6. Determine whether the problem is connectivity, event retrieval, event application, or data inconsistency.
7. Review disk space, resource usage, and binary log retention.
8. Choose a recovery action based on the actual error and the data consistency requirements.
9. Verify that replication has resumed and that lag is decreasing.

Important: Do not skip arbitrary replication errors just to make the status appear healthy. Skipping transactions can leave the replica inconsistent with the source. Identify the cause and choose a documented recovery procedure.

19. Failover and High Availability Fundamentals

Failover means moving database service to another server when the current source becomes unavailable or unsuitable. Replication can support failover by maintaining copies of source data, but a reliable failover process requires more than replication alone.

19.1 Basic Failover Considerations

• Determine which replica has the most complete and current transaction history.
• Assess the possibility of transactions missing from replicas.
• Prevent conflicting writes to old and new sources.
• Update application routing or connection endpoints safely.
• Verify database consistency before restoring normal service.
• Plan how the old source will rejoin the topology.
• Monitor replication and application health after failover.

19.2 Manual and Automated Failover

Manual failover is initiated by an administrator following a documented procedure. It provides human control but may take longer.

Automated failover uses orchestration or high-availability tooling to detect failures and promote an appropriate server. It can reduce recovery time, but incorrect failure detection or split-brain scenarios can create serious consistency risks.

19.3 Recovery Objectives

Replication design should reflect the business's acceptable data loss and downtime, not just the number of replicas available.

20. Practical Lab: Understanding a Source-to-Replica Setup

This lab outlines the stages involved in creating a simple source-to-replica environment. It is intended for a controlled development environment. Production deployments require version-specific procedures, security review, initial data provisioning, monitoring, and recovery planning.

Step 1: Prepare Two MySQL Servers

Set up two compatible MySQL server instances that can communicate over a protected network. Assign each server a unique server ID.

Example source configuration:

[mysqld]
server_id=101
log_bin=mysql-bin

Example replica configuration:

[mysqld]
server_id=102
relay_log=replica-relay-bin

These are basic illustrative options, not complete configuration files. Check the requirements of the installed version and any existing settings before applying them.

Step 2: Configure the Replication Account

Create a dedicated account on the source with the privileges needed for replication. Restrict the account to the replica's expected network address and configure encrypted connections when required.

Step 3: Provision the Replica's Initial Data

Before replication can keep data synchronized, the replica needs an appropriate initial copy of the source data. Use a consistent backup or a documented provisioning method suitable for the database size, storage engines, and selected replication approach.

Step 4: Configure the Source Connection

On the replica, configure the source connection with the correct host, port, replication account, and either the appropriate binary log coordinates or GTID auto-positioning, depending on the chosen method.

For current MySQL versions, the command family includes CHANGE REPLICATION SOURCE TO. The precise options depend on the configuration and replication method.

Step 5: Start Replication

After validating the initial data and source configuration, start replication using the supported command for the installed version, such as:

START REPLICA;

Do not run this command until the required source connection and positioning information are correctly configured.

Step 6: Check Replication Status

SHOW REPLICA STATUS\G

Check the receiver and applier states, error fields, source connection information, and lag indicators where available.

Step 7: Test with a Sample Table

In an isolated test database, create a sample table and insert a row on the source:

CREATE DATABASE IF NOT EXISTS replication_lab;
USE replication_lab;

CREATE TABLE IF NOT EXISTS demo_customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

INSERT INTO demo_customers (customer_id, customer_name)
VALUES (1, 'Asha')
ON DUPLICATE KEY UPDATE customer_name = 'Asha';

On the replica, query the table after the replication process has applied the change:

SELECT * FROM replication_lab.demo_customers;

The row should appear if the database and table are included in replication, the setup is correct, and the replica has caught up. If it does not appear, inspect replication status and error logs rather than repeating setup commands blindly.

Step 8: Document the Results

• Record each server's role and server ID.
• Record the replication method used.
• Verify the sample row on the replica.
• Check the receiver and applier states.
• Record any replication lag or errors.
• Document how the replica would be restored or reprovisioned if it became inconsistent.

21. Replication Best Practices

1. Use compatible versions: Confirm replication compatibility before upgrading or mixing versions.
2. Assign unique server IDs: Avoid conflicting server identities within the topology.
3. Protect replication connections: Use dedicated accounts, least privilege, and appropriate network encryption.
4. Monitor replication continuously: Alert on stopped threads, increasing lag, errors, and low disk space.
5. Retain binary logs appropriately: Ensure required logs remain available for replicas and recovery procedures.
6. Keep initial data consistent: Provision replicas from a known, consistent data copy.
7. Plan for replica lag: Route reads according to the application's consistency requirements.
8. Test recovery procedures: A replica is useful only if the organization can safely use it when needed.
9. Maintain independent backups: Use verified backups and test restoration regularly.
10. Avoid uncontrolled writes: Prevent accidental writes on replicas when the architecture expects them to be read-only.
11. Investigate errors before recovery: Avoid skipping transactions without understanding the data impact.
12. Document topology changes: Record promotions, source changes, server replacements, and replication configuration updates.

22. Hands-On Exercises

Exercise 1: Explain Replication Flow

Draw a diagram showing the source, binary log, replica receiver, relay log, and replica applier. Explain the responsibility of each component.

Exercise 2: Compare Replication Formats

Describe statement-based, row-based, and mixed-based replication. Give one potential advantage and one limitation of each.

Exercise 3: Understand GTIDs

Explain how GTIDs identify transactions and why GTID auto-positioning can simplify replica configuration.

Exercise 4: Investigate Replication Lag

Assume a reporting replica is several minutes behind the source. List at least five possible causes and explain which status fields or system metrics you would inspect.

Exercise 5: Design a Read-Scaling Architecture

Design an architecture for an online store with one source and two replicas. Decide which queries should be sent to the source and which may be sent to replicas. Explain how the application will handle read-after-write requirements.

Exercise 6: Plan for Source Failure

Write a failover checklist that considers data freshness, promotion, routing, prevention of conflicting writes, and validation after recovery.

Exercise 7: Replication vs Backup

Explain why an accidental deletion on the source may also affect replicas and describe how independent backups and point-in-time recovery can help.

23. Interview Questions and Answers

Q1. What is MySQL replication?

Answer: MySQL replication copies database changes from a source server to one or more replicas.

Q2. What is a source server?

Answer: The source is the server that records changes in its binary log for replicas to retrieve.

Q3. What is a replica server?

Answer: A replica receives replication events and applies them to maintain a copy of selected source data.

Q4. What is a binary log?

Answer: It is a log of events describing database changes and selected operations. It is used for replication and can support point-in-time recovery workflows.

Q5. What is a relay log?

Answer: It is a log on the replica that stores events received from the source before they are applied.

Q6. What is replication lag?

Answer: Replication lag is the delay between a source change and its application on a replica.

Q7. What is asynchronous replication?

Answer: It is replication in which the source does not wait for a replica acknowledgment before returning the commit response to the client.

Q8. What is semisynchronous replication?

Answer: It adds a configured replica acknowledgment requirement to the source's commit process. It can reduce some data-loss risks but may increase latency.

Q9. What is row-based replication?

Answer: It records row changes in the binary log, allowing the replica to apply the changed rows rather than relying only on re-executing the original SQL statement.

Q10. What is statement-based replication?

Answer: It records SQL statements that modify data, and the replica executes those statements to reproduce changes.

Q11. What is mixed-based replication?

Answer: It allows MySQL to select statement-based or row-based logging according to its rules for the operation.

Q12. What is a GTID?

Answer: A Global Transaction Identifier uniquely identifies a transaction within a replication topology and helps MySQL track which transactions have been executed.

Q13. What is GTID auto-positioning?

Answer: It allows a replica and source to use transaction history to determine which transactions are still required, reducing the need to configure binary log file positions manually.

Q14. Can replication replace backups?

Answer: No. Replication can copy accidental or malicious changes to replicas. Independent backups and tested restoration procedures remain necessary.

Q15. How can replication improve read performance?

Answer: Suitable read queries can be distributed across replicas, reducing some of the read workload on the source.

Q16. What causes replication lag?

Answer: Common causes include high write volume, large transactions, network latency, insufficient replica resources, locks, and replication errors.

Q17. How do you check replication status?

Answer: In supported versions, use SHOW REPLICA STATUS\G and inspect receiver status, applier status, error fields, and lag indicators.

Q18. What is failover?

Answer: Failover is the process of transferring database service to another server when the current source becomes unavailable or unsuitable.

Q19. What is multi-source replication?

Answer: It allows one replica to receive transactions from multiple sources through separate replication channels.

Q20. What is Group Replication?

Answer: It is a MySQL technology that coordinates a group of servers and supports single-primary or multi-primary architectures under its own consistency and membership rules.

Q21. Why should replication accounts use limited privileges?

Answer: Least privilege reduces the potential damage if replication credentials are exposed or misused.

Q22. What should you do when the applier stops?

Answer: Inspect the reported error, determine the cause, compare the relevant source and replica data, and apply a documented corrective procedure.

Q23. Why is read-after-write consistency important?

Answer: A lagging replica may not yet show a transaction that an application just committed. Routing or consistency controls are needed when immediate visibility is required.

Q24. What are RPO and RTO?

Answer: RPO defines the acceptable data-loss window, while RTO defines the target time to restore service.

Q25. What are the key requirements for a reliable replication system?

Answer: Correct configuration, secure connectivity, consistent initial data, suitable log retention, continuous monitoring, independent backups, and tested recovery procedures.

24. Quick Revision Checklist

• Replication copies changes from a source to one or more replicas.
• The source records relevant events in its binary log.
• The replica receives events and stores them in relay logs.
• The applier processes replicated changes on the replica.
• Asynchronous replication is the traditional default in MySQL.
• Semisynchronous replication waits for a configured replica acknowledgment.
• Statement-based, row-based, and mixed-based logging formats are available.
• GTIDs identify transactions and can simplify replication positioning.
• Common topologies include single-source, multiple replicas, chains, and multi-source configurations.
• Replication lag can cause stale reads and affect recovery decisions.
• Replication status and error logs help diagnose problems.
• Replication must be secured with appropriate accounts, privileges, and network protections.
• Replication does not replace independent backups.
• Failover requires a planned procedure and careful validation.

25. Conclusion

MySQL replication is an important database technology for maintaining additional copies of data, distributing suitable read workloads, supporting reporting, and contributing to disaster recovery and high-availability architectures.

Understanding the source, replica, binary log, relay log, replication threads, logging formats, GTIDs, and replication lag provides the foundation for managing replicated MySQL systems. A successful implementation also requires secure configuration, monitoring, reliable initial data provisioning, independent backups, and tested recovery procedures.

Replication should be designed around application requirements, consistency expectations, acceptable data loss, and recovery time targets. No single replication topology is ideal for every workload; the correct design depends on the environment and the business objectives.

26. Official Learning Resources

• MySQL 8.4 Reference Manual – Replication Overview
• MySQL 8.4 Reference Manual – Configuring Replication
• MySQL 8.4 Reference Manual – GTID-Based Replication

Continue Learning: Explore the MySQL Training Course at JustAcademy for more database topics. You can also visit the JustAcademy Course Demo Registration Page to register for a demo session.